<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Penilaian_model extends CI_Model {

	function __construct()
	{
		parent::__construct();
		$this->CI = get_instance();
	}
	
	//==================================================================
	function getGuru(){
		$this->db->select('*');
		$this->db->from('tb_pegawai');
		// $this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('ISAKTIF',1);
		$this->db->where('IS_AKADEMIK',1);
		$query = $this->db->get();
		return $query;
	}
	
	function getUjian($id_sekolah, $id_ta, $kode_mp){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_jenis_ujian');
		$this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('TAHUN_AJARAN',$id_ta);
		$this->db->where('KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->order_by('ID_JENIS_UJIAN', 'asc'); 
		$query = $this->db->get();
		return $query;
	}
	
	function getNilai($id_sekolah, $id_ta, $id_tingkat, $id_kelas, $id_smt, $kode_mp){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_nilai_siswa a');
		$this->db->join('tb_pelajaran_jenis_ujian b','b.KODE_UJIAN=a.KODE_UJIAN');
		$this->db->where('a.ID_SEKOLAH',$id_sekolah);
		$this->db->where('a.TAHUN_AJARAN',$id_ta);
		$this->db->where('a.ID_TINGKAT',$id_tingkat);
		$this->db->where('a.ID_KELAS',$id_kelas);
		$this->db->where('a.ID_SEMESTER',$id_smt);
		$this->db->where('a.KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->order_by('NISN,ID_JENIS_UJIAN', 'asc');
		$query = $this->db->get();
		return $query;
	}
	
	function get_siswa_kelas($id_sekolah,$id_tahunajaran,$id_tingkat,$id_kelas){
		if($id_tingkat != '' && $id_kelas != ''){
			$this->db->select('*');
			$this->db->from('tb_siswa_kelas a');
			$this->db->join('tb_siswa b','a.NISN=b.NISN','left'); 
			$this->db->where('a.ID_SEKOLAH',$id_sekolah);
			$this->db->where('a.ID_TAHUNAJARAN',$id_tahunajaran);
			$this->db->where('a.ID_TINGKAT',$id_tingkat);
			$this->db->where('a.ID_KELAS',$id_kelas);
			$this->db->where('b.ISAKTIF',1);
			$this->CI->flexigrid->build_query();
			
			$return['records'] = $this->db->get();
			
			$this->db->select('*');
			$this->db->from('tb_siswa_kelas a'); 
			$this->db->join('tb_siswa b','a.NISN=b.NISN','left'); 
			$this->db->where('a.ID_SEKOLAH',$id_sekolah);
			$this->db->where('a.ID_TAHUNAJARAN',$id_tahunajaran);
			$this->db->where('a.ID_TINGKAT',$id_tingkat);
			$this->db->where('a.ID_KELAS',$id_kelas);
			$this->db->where('b.ISAKTIF',1);
			$this->CI->flexigrid->build_query(FALSE);
			
			$return['record_count'] = $this->db->count_all_results();
		}
		else {
			// $return['records'] = array();
			$return['record_count'] = 0;
		}
		return $return;
	}
	
	function get_nilai_siswa($id_sekolah, $id_tingkat='',$id_kelas='',$id_smt='', $nip_guru=''){
		$this->db->select('*');
		$this->db->from('tb_master_sekolah');
		$this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('ISAKTIF',1);
		$query_dept = $this->db->get()->row()->ID_DEPARTMENT;
		
		$this->db->select('ID_TA');
		$this->db->from('tb_master_tahun_ajaran');
		$this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('DEPARTMENT',$query_dept);
		$this->db->where('ISAKTIF',1);
		$query_ta = $this->db->get()->row()->ID_TA;
		
		$this->db->select('nil.KODE_PENILAIAN, nil.ASPEK_PENILAIAN, pel.NAMA_MATA_PELAJARAN, pel.KODE_MATA_PELAJARAN, peg.GELAR_DEPAN, peg.NAMA_LENGKAP,peg.GELAR_BELAKANG, t.TINGKAT,t.ID_TINGKAT,k.KELAS,k.ID_KELAS,smt.SEMESTER,smt.ID_SMT, p.*, p.ID_SEKOLAH as ID_SEK, s.NAMA_SEKOLAH, a.TAHUN_AJARAN as NAMA_TA, p.TAHUN_AJARAN as ID_TA, p.KETERANGAN as KET');
		$this->db->from('tb_pelajaran_guru p'); 
		$this->db->join('tb_master_sekolah s','s.ID_SEKOLAH=p.ID_SEKOLAH');
		$this->db->join('tb_master_tahun_ajaran a','a.ID_TA=p.TAHUN_AJARAN');
		$this->db->join('tb_master_tingkat t','t.ID_TINGKAT=p.ID_TINGKAT');
		$this->db->join('tb_master_kelas k','k.ID_KELAS=p.ID_KELAS');
		$this->db->join('tb_master_semester smt','smt.ID_SMT=p.ID_SEMESTER');
		$this->db->join('tb_pelajaran pel','pel.KODE_MATA_PELAJARAN=p.KODE_MATA_PELAJARAN and pel.TAHUN_AJARAN=p.TAHUN_AJARAN');
		$this->db->join('tb_pegawai peg','peg.NIP=p.NIP');
		$this->db->join('tb_pelajaran_aspek_nilai nil','nil.KODE_MATA_PELAJARAN=p.KODE_MATA_PELAJARAN and nil.TAHUN_AJARAN=p.TAHUN_AJARAN');
		$this->db->where('p.TAHUN_AJARAN',$query_ta);
		$this->db->where('p.ID_SEKOLAH',$id_sekolah);
		// if($id_tingkat!='' and $id_kelas!='' and $id_smt!=''){
			$this->db->where('p.ID_TINGKAT',$id_tingkat);
			$this->db->where('p.ID_KELAS',$id_kelas);
			$this->db->where('p.ID_SEMESTER',$id_smt);
			$this->db->where('p.NIP',$nip_guru);
		// }
		// $this->db->group_by('p.KODE'); 
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('nil.KODE_PENILAIAN, nil.ASPEK_PENILAIAN, pel.NAMA_MATA_PELAJARAN, pel.KODE_MATA_PELAJARAN, peg.GELAR_DEPAN, peg.NAMA_LENGKAP,peg.GELAR_BELAKANG, t.TINGKAT,t.ID_TINGKAT,k.KELAS,k.ID_KELAS,smt.SEMESTER,smt.ID_SMT, p.*, p.ID_SEKOLAH as ID_SEK, s.NAMA_SEKOLAH, a.TAHUN_AJARAN as NAMA_TA, p.TAHUN_AJARAN as ID_TA, p.KETERANGAN as KET');
		$this->db->from('tb_pelajaran_guru p'); 
		$this->db->join('tb_master_sekolah s','s.ID_SEKOLAH=p.ID_SEKOLAH');
		$this->db->join('tb_master_tahun_ajaran a','a.ID_TA=p.TAHUN_AJARAN');
		$this->db->join('tb_master_tingkat t','t.ID_TINGKAT=p.ID_TINGKAT');
		$this->db->join('tb_master_kelas k','k.ID_KELAS=p.ID_KELAS');
		$this->db->join('tb_master_semester smt','smt.ID_SMT=p.ID_SEMESTER');
		$this->db->join('tb_pelajaran pel','pel.KODE_MATA_PELAJARAN=p.KODE_MATA_PELAJARAN and pel.TAHUN_AJARAN=p.TAHUN_AJARAN');
		$this->db->join('tb_pegawai peg','peg.NIP=p.NIP');
		$this->db->join('tb_pelajaran_aspek_nilai nil','nil.KODE_MATA_PELAJARAN=p.KODE_MATA_PELAJARAN and nil.TAHUN_AJARAN=p.TAHUN_AJARAN');
		$this->db->where('p.TAHUN_AJARAN',$query_ta);
		$this->db->where('p.ID_SEKOLAH',$id_sekolah);
		// if($id_tingkat!='' and $id_kelas!='' and $id_smt!=''){
			$this->db->where('p.ID_TINGKAT',$id_tingkat);
			$this->db->where('p.ID_KELAS',$id_kelas);
			$this->db->where('p.ID_SEMESTER',$id_smt);
			$this->db->where('p.NIP',$nip_guru);
			// $this->db->where('p.TANGGAL',$tglpresensi);
		// }
		// $this->db->group_by('p.TANGGAL'); 
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}
	
	function get_siswa_kelas2($id_sekolah, $id_ta, $id_tingkat, $id_kelas){
		$this->db->select('*');
		$this->db->from('tb_siswa_kelas a'); 
		$this->db->join('tb_siswa b','a.NISN=b.NISN','left'); 
		$this->db->where('a.ID_SEKOLAH',$id_sekolah);
		$this->db->where('a.ID_TAHUNAJARAN',$id_ta);
		$this->db->where('a.ID_TINGKAT',$id_tingkat);
		$this->db->where('a.ID_KELAS',$id_kelas);
		$query = $this->db->get();
		return $query;
	}
	
	function insert_nilaisiswa($data){
		$this->db->insert('tb_pelajaran_nilai_siswa',$data);
	}
	
	function delete_nilaisiswa($id_sekolah, $id_ta,$id_tingkat,$id_kelas,$id_semester,$kode_mp){
		$this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('TAHUN_AJARAN',$id_ta);
		$this->db->where('ID_TINGKAT',$id_tingkat);
		$this->db->where('ID_KELAS',$id_kelas);
		$this->db->where('ID_SEMESTER',$id_semester);
		$this->db->where('KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->delete('tb_pelajaran_nilai_siswa'); 
	}
	
	//==================================================================
	
	function getNilairaport($id_sekolah, $id_ta, $id_tingkat, $id_kelas, $id_smt, $kode_mp){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_nilai_raport_siswa a');
		$this->db->where('a.ID_SEKOLAH',$id_sekolah);
		$this->db->where('a.TAHUN_AJARAN',$id_ta);
		$this->db->where('a.ID_TINGKAT',$id_tingkat);
		$this->db->where('a.ID_KELAS',$id_kelas);
		$this->db->where('a.ID_SEMESTER',$id_smt);
		$this->db->where('a.KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->order_by('NISN,ID_RAPORT', 'asc');
		$query = $this->db->get();
		return $query;
	}
	function insert_nilairaport($data){
		$this->db->insert('tb_pelajaran_nilai_raport_siswa',$data);
	}
	function delete_nilairaport($id_sekolah, $id_ta,$id_tingkat,$id_kelas,$id_semester,$kode_mp){
		$this->db->where('ID_SEKOLAH',$id_sekolah);
		$this->db->where('TAHUN_AJARAN',$id_ta);
		$this->db->where('ID_TINGKAT',$id_tingkat);
		$this->db->where('ID_KELAS',$id_kelas);
		$this->db->where('ID_SEMESTER',$id_semester);
		$this->db->where('KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->delete('tb_pelajaran_nilai_raport_siswa'); 
	}
	
	//==================================================================
	
	function getRaportSiswa($id_sekolah, $id_ta, $id_tingkat, $id_kelas, $id_smt, $nisn){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_nilai_raport_siswa a');
		$this->db->join('tb_pelajaran b','a.KODE_MATA_PELAJARAN=b.KODE_MATA_PELAJARAN and a.TAHUN_AJARAN=b.TAHUN_AJARAN');
		$this->db->where('a.ID_SEKOLAH',$id_sekolah);
		$this->db->where('a.TAHUN_AJARAN',$id_ta);
		$this->db->where('a.ID_TINGKAT',$id_tingkat);
		$this->db->where('a.ID_KELAS',$id_kelas);
		$this->db->where('a.ID_SEMESTER',$id_smt);
		$this->db->where('a.NISN',$nisn);
		// $this->db->order_by('ID_RAPORT', 'asc');
		$query = $this->db->get();
		return $query;
	}
	
	function getGrading($id_sekolah, $id_ta){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_grading a');
		$this->db->where('a.ID_SEKOLAH',$id_sekolah);
		$this->db->where('a.TAHUN_AJARAN',$id_ta);
		$this->db->order_by('ID_GRADE', 'asc');
		$query = $this->db->get();
		return $query;
	}
	
	function cari_grade($kode_mp,$nilai){
		$this->db->select('*');
		$this->db->from('tb_pelajaran_grading');
		$this->db->where('TAHUN_AJARAN',$this->session->userdata('tahunajaran'));
		$this->db->where('ID_SEKOLAH',$this->session->userdata('unitid'));
		$this->db->where('KODE_MATA_PELAJARAN',$kode_mp);
		$this->db->where('NILAI_MIN <',$nilai);
		$this->db->where('NILAI_MAX >=',$nilai);
		$query = $this->db->get();
		return $query;
	}
}

?>
